#!/usr/bin/env python3
import logging
import os
import sys

import pymysql
import tenacity

# 添加项目根目录到sys.path以便导入common模块
project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, project_root)

from common.config import settings

# 日志配置：确保日志目录存在
log_dir = os.path.join(os.path.dirname(__file__), '..', 'logs')
os.makedirs(log_dir, exist_ok=True)

logging.basicConfig(
	level=logging.INFO,
	format="%(asctime)s %(levelname)s %(message)s",
	handlers=[
		logging.StreamHandler(),
		logging.FileHandler(os.path.join(log_dir, "freeze.log"), encoding='utf-8')
	])

# 从配置对象获取数据库连接参数
HOST = settings.db_host
PORT = settings.db_port
USER = settings.db_user
PWD = settings.db_password
DB = settings.db_name

SQL = """
INSERT INTO dc_meter_freeze(serial_no, freeze_day, total_kwh, daily_kwh)
WITH ordered AS (
        SELECT 
            meter_code,
            total_kwh,
            ts,
            ROW_NUMBER() OVER (PARTITION BY meter_code ORDER BY ts ASC)  AS rn_first,
            ROW_NUMBER() OVER (PARTITION BY meter_code ORDER BY ts DESC) AS rn_last
        FROM   dc_meter_telemetry
        WHERE  ts >= CURDATE() - INTERVAL 1 DAY
          AND  ts <  CURDATE()
          AND  quality = 'GOOD'
),
Daily AS (
        SELECT  
            meter_code,
            MAX(CASE WHEN rn_first = 1 THEN total_kwh END) AS first_kwh,   -- 昨日第一条记录
            MAX(CASE WHEN rn_last  = 1 THEN total_kwh END) AS last_kwh    -- 昨日最后一条记录
        FROM ordered
        GROUP BY meter_code
)
SELECT  
        meter_code,
        CURDATE() - INTERVAL 1 DAY        AS freeze_day,
        last_kwh                          AS total_kwh,   -- 昨日24点累计电量（按最后一条记录）
        GREATEST(last_kwh - first_kwh, 0) AS daily_kwh    -- 日用电量 = 昨日最后 - 昨日第一条
FROM    Daily
WHERE   first_kwh IS NOT NULL AND last_kwh IS NOT NULL
ON DUPLICATE KEY UPDATE
        total_kwh = VALUES(total_kwh),
        daily_kwh = VALUES(daily_kwh);"""


@tenacity.retry(stop=tenacity.stop_after_attempt(3), wait=tenacity.wait_fixed(5))
def run_freeze():
	logging.info(f"Database: {HOST}:{PORT}/{DB}")

	try:
		conn = pymysql.connect(host=HOST, port=PORT, user=USER, password=PWD, db=DB,
							   charset='utf8mb4', autocommit=True)
		logging.info("Executing freeze SQL...")
		with conn.cursor() as cur:
			cur.execute(SQL)
			affected = cur.rowcount
			logging.info(f"freeze affected rows: {affected}")

		conn.close()

	except Exception as e:
		logging.error(f"Freeze job failed: {e}", exc_info=True)
		raise


if __name__ == '__main__':
	run_freeze()
